﻿--•	BẢNG SERVER12.ANHCP.DBO.[DOANH_SO_TTR_LC_DP]
--PHÂN CHIA LC, NHỜ THU, CHUYỂN TIỀN TTR – THEO TRƯỜNG LOAI_GIAO_DICH
 
 DELETE FROM DOANH_SO_TTR_LC_DP_RAW

 INSERT DOANH_SO_TTR_LC_DP_RAW
 SELECT A.CIF, B.DAO, A.DEBIT_AMOUNT, A.ID, A.DEBIT_VALUE_DATE,A.LOAI_GIAO_DICH,A.PRODUCT_NAME,A.YEAR_MONTH, A.AMT_BILL,ROW_NUMBER() OVER( PARTITION BY A.ID ORDER BY A.DEBIT_VALUE_DATE DESC) AS RN
 --into DOANH_SO_TTR_LC_DP_RAW
  FROM SERVER12.ANHCP.DBO.[DOANH_SO_TTR_LC_DP] A, TBL_CUSTOMER B
  WHERE A.CIF = B.CIF 

  select * from DOANH_SO_TTR_LC_DP_RAW

  drop table TRADE_BY_CIF_RAW

  --update DOANH_SO_TTR_LC_DP_RAW set isnew = 0

SELECT CIF, DAO, LOAI_GIAO_DICH, PRODUCT_NAME, YEAR_MONTH, SUM(AMT_BILL*1e9) as balance,ROW_NUMBER() OVER( PARTITION BY cif ORDER BY year_month) AS RN
  INTO TRADE_BY_CIF_RAW
  FROM DOANH_SO_TTR_LC_DP_RAW 
  WHERE RN=1 
  GROUP BY CIF, DAO, LOAI_GIAO_DICH, PRODUCT_NAME, YEAR_MONTH

  select * from TRADE_BY_CIF_RAW order by  cif,rn, year_month

  DROP TABLE TRADE_BY_DAO_RAW

  select DAO, LOAI_GIAO_DICH, PRODUCT_NAME, YEAR_MONTH, SUM(balance) as balance,count(cif) as no_cif, 0 as new_cif
  INTO TRADE_BY_DAO_RAW
  FROM TRADE_BY_CIF_RAW 
  WHERE YEAR_MONTH > '201510'
  GROUP BY DAO, LOAI_GIAO_DICH, PRODUCT_NAME, YEAR_MONTH

  UPDATE A
  SET A.new_cif=B.new_cif
  FROM TRADE_BY_DAO_RAW A, (select DAO, LOAI_GIAO_DICH, PRODUCT_NAME, YEAR_MONTH, SUM(balance) as balance, count(cif) as new_cif
  FROM TRADE_BY_CIF_RAW 
  WHERE YEAR_MONTH > '201510' AND RN=1
  GROUP BY DAO, LOAI_GIAO_DICH, PRODUCT_NAME, YEAR_MONTH) B
  WHERE A.DAO = B.DAO AND A.LOAI_GIAO_DICH= B.LOAI_GIAO_DICH AND A.YEAR_MONTH = B.YEAR_MONTH


  select * from TRADE_BY_DAO_RAW

--•	BẢNG SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH -> BAO LANH
 
 SELECT MONTH,* FROM SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH WHERE convert(nvarchar(7), FROM_DATE,120) =MONTH

 SELECT A.CUST_ID AS CIF, B.DAO,  A.MD_ID, A.VALUE_DATE,'GUARRANTEE' AS LOAI_GIAO_DICH,A.CATEGORY,convert(nvarchar(6), A.FROM_DATE,112) AS YEAR_MONTH, A.AMOUNT_QD
 into DOANH_SO_BAO_LANH_RAW
  FROM SERVER12.TRANGLT9.DBO.DOANH_SO_BAO_LANH A, TBL_CUSTOMER B
  WHERE A.CUST_ID = B.CIF 

  SELECT * FROM DOANH_SO_BAO_LANH_RAW

  
SELECT CIF, DAO, LOAI_GIAO_DICH, CATEGORY, YEAR_MONTH, SUM(AMOUNT_QD*1e9) as balance,ROW_NUMBER() OVER( PARTITION BY cif ORDER BY year_month) AS RN
  INTO TRADE_BL_BY_CIF_RAW
  FROM DOANH_SO_BAO_LANH_RAW 
  GROUP BY CIF, DAO, LOAI_GIAO_DICH, CATEGORY, YEAR_MONTH

  

  SELECT * FROM TRADE_BY_DAO_RAW

  INSERT TRADE_BY_DAO_RAW
  select DAO, LOAI_GIAO_DICH, CONVERT(NVARCHAR(50),CATEGORY), YEAR_MONTH, SUM(balance) as balance,count(cif) as no_cif, 0 as new_cif
  --INTO TRADE_BY_DAO_RAW
  FROM TRADE_BL_BY_CIF_RAW 
  WHERE YEAR_MONTH > '201510'
  GROUP BY DAO, LOAI_GIAO_DICH, CATEGORY, YEAR_MONTH


  
  UPDATE A
  SET A.new_cif=B.new_cif
  FROM TRADE_BY_DAO_RAW A, (select DAO, LOAI_GIAO_DICH, CATEGORY, YEAR_MONTH, SUM(balance) as balance, 0 as no_cif,count(cif) as new_cif
  --INTO TRADE_BY_DAO_RAW
  FROM TRADE_BL_BY_CIF_RAW 
  WHERE YEAR_MONTH > '201510' AND RN =1 
  GROUP BY DAO, LOAI_GIAO_DICH, CATEGORY, YEAR_MONTH) B
  WHERE A.DAO = B.DAO AND A.LOAI_GIAO_DICH= B.LOAI_GIAO_DICH AND A.YEAR_MONTH = B.YEAR_MONTH AND A.PRODUCT_NAME = B.CATEGORY


  SELECT YEAR_MONTH AS YEARMONTH, DAO, 1 AS GROUP_TYPE, LOAI_GIAO_DICH + ' - ' + PRODUCT_NAME AS PRODUCT, NO_CIF AS QUANTITY, BALANCE
  INTO TRADE_PERFORMANCE_RAW
    FROM TRADE_BY_DAO_RAW

	INSERT TRADE_PERFORMANCE_RAW
  SELECT YEAR_MONTH AS YEARMONTH, DAO, 1 AS GROUP_TYPE, LOAI_GIAO_DICH + ' - ' + PRODUCT_NAME AS PRODUCT, NEW_CIF AS QUANTITY, 0 AS BALANCE
  --INTO TRADE_PERFORMANCE_RAW
    FROM TRADE_BY_DAO_RAW


	SELECT * FROM TRADE_PERFORMANCE_RAW
  
 DECLARE @MONTH_1 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -1, GETDATE())),112)
DECLARE @MONTH2 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -2, GETDATE())),112)
DECLARE @MONTH3 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -3, GETDATE())),112)
DECLARE @MONTH4 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -4, GETDATE())),112)
DECLARE @MONTH5 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -5, GETDATE())),112)
DECLARE @MONTH6 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -6, GETDATE())),112)
DECLARE @MONTH7 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -7, GETDATE())),112)
DECLARE @MONTH8 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -8, GETDATE())),112)
DECLARE @MONTH9 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -9, GETDATE())),112)
DECLARE @MONTH10 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -10, GETDATE())),112)
DECLARE @MONTH11 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -11, GETDATE())),112)
DECLARE @MONTH0 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -12, GETDATE())),112)
 
EXEC('
SELECT dao, product, group_type, col, ['+@MONTH0+'] as [month0],['+@MONTH_1+'] as [month1],['+@MONTH2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11]
INTO TBL_SP_TRADE_EOP
FROM
(SELECT YEARMONTH, DAO,PRODUCT,GROUP_TYPE, COL, VALUE FROM TRADE_PERFORMANCE_RAW
CROSS APPLY
( SELECT ''QUANTITY'', CAST(QUANTITY AS NUMERIC) UNION ALL
  SELECT ''BALANCE'', CAST(BALANCE AS NUMERIC) ) C(COL, VALUE)) D
PIVOT
( MAX(VALUE)
  FOR YEARMONTH IN (['+@MONTH_1+'],['+@MONTH2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) PIV;
')

declare @count int
  set @count = 0

  while (@count <12)
  begin 
  exec('
  update TBL_SP_TRADE_EOP set month'+@count+'=0 where MONTH'+@count+' is null

  update TBL_SP_TRADE_EOP set month'+@count+'=0 where col=''balance'' and group_type=2
  ')
  set @count = @count+1
  end
  
-- delete from  [SERVER12].[SMECustomer360].dbo.TBL_SP_TRADE_EOP

--insert [SERVER12].[SMECustomer360].dbo.TBL_SP_TRADE_EOP
--select * from TBL_SP_TRADE_EOP


  --SELECT * FROM TBL_SP_TRADE_EOP
